--school
if OBJECT_ID('school') is not null drop table school
go
create table school(
	id int not null,
	name nvarchar(255) not null,
	constraint pk_school primary key (id)
)

go

--facuty
if OBJECT_ID('faculty') is not null drop table faculty
go
create table faculty(
	id int not null,
	mane nvarchar(255) not null,
	constraint pk_faculty primary key (id)
)

go

--course
if OBJECT_ID('course') is not null drop table course
go
if exists ( select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_course_school') and parent_object_id = OBJECT_ID(N'course'))
alter table course drop constraint fk_course_school
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_course_faculty') and parent_object_id = OBJECT_ID(N'course'))
alter table course drop constraint fk_course_faculty
go
create table course(
	id int not null,
	name nvarchar(255) not null,
	faculty_id int not null,
	school_id int not null,
	manager int not null,
	startdate date not null,
	coursedescription nvarchar(1000),
	constraint pk_course primary key (id),
	constraint fk_course_school foreign key (school_id) references school(id),
	constraint fk_course_faculty foreign key (faculty_id) references faculty(id)
)

go

--coursemembers
if OBJECT_ID('coursemembers') is not null drop table coursemembers
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_coursemembers_course') and parent_object_id = OBJECT_ID(N'coursemembers'))
alter table coursemembers drop constraint fk_coursemembers_course
go
create table coursemembers(
	course_id int not null,
	cuser_id int not null,
	usertype tinyint not null,
	constraint pk_coursemembers primary key (course_id, cuser_id),
	constraint fk_coursemembers_course foreign key (course_id) references course(id)
)

go

--event
if OBJECT_ID('courseevent') is not null drop table courseevent
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'event') and parent_object_id = OBJECT_ID(N'event'))
alter table courseevent drop constraint fk_courseevent_course
go
create table courseevent(
	id int not null,
	course_id int not null,
	name nvarchar(255) not null,
	evnetdescription nvarchar(1000),
	startdate date not null,
	duedate date not null,
	deadline time(7) not null,
	link nvarchar(1000) not null,
	constraint pk_courseevent primary key (id),
	constraint fk_courseevent_course foreign key (course_id) references course(id)
)

go

--relationship
if OBJECT_ID('relationship') is not null drop table relationship
go
create table relationship(
	usera_id int not null,
	userb_id int not null,
	constraint pk_relationship primary key (usera_id, userb_id)
)